from fastapi import FastAPI, HTTPException
from sqlalchemy import create_engine, text
import pandas as pd
import numpy as np
from typing import List, Dict, Any
import json
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'database': 'books_db',
    'user': 'root',
    'password': '******',
    'port': 3306,
    'charset': 'utf8mb4'
}

# 创建FastAPI应用
app = FastAPI(title="书籍数据分析API", description="提供书籍数据的各类分析结果")

# 允许跨域
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # 替换为你的前端地址
    allow_credentials=True,
    allow_methods=["*"],  # 允许所有HTTP方法（GET/POST等）
    allow_headers=["*"],  # 允许所有请求头
)

# 创建数据库引擎
engine = create_engine(
    f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}?charset={DB_CONFIG['charset']}"
)


def get_db_connection():
    """获取数据库连接"""
    try:
        connection = engine.connect()
        return connection
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"数据库连接失败: {str(e)}")


@app.on_event("shutdown")
def shutdown_event():
    """应用关闭时释放资源"""
    if 'engine' in locals():
        engine.dispose()


### 一、基础描述性统计接口 ###

@app.get("/api/analysis/top3_comments", summary="获取评论数TOP3的书籍")
def get_top3_comments():
    """获取评论数最多的3本书籍及其共性分析"""
    try:
        connection = get_db_connection()
        sql = """
        SELECT title, author, publisher, price, comments
        FROM books
        ORDER BY comments DESC
        LIMIT 3
        """
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        # 分析共性
        results = []
        for _, row in df.iterrows():
            result = row.to_dict()
            # 简单分析共性（实际应用中可更复杂）
            if "茅盾" in result["author"]:
                result["analysis"] = "可能与作者IP知名度有关"
            elif result["price"] < 10:
                result["analysis"] = "可能因低价策略吸引更多评论"
            else:
                result["analysis"] = "可能因内容质量或宣传力度大"
            results.append(result)

        return {
            "status": "success",
            "data": results,
            "message": "评论数TOP3的书籍"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取数据失败: {str(e)}")

# 出版社总数统计接口
@app.get("/api/analysis/publisher_count", summary="获取出版社总数")
def get_publisher_count():
    try:
        connection = get_db_connection()
        sql = "SELECT COUNT(DISTINCT publisher) AS total_publishers FROM books"
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        return {
            "status": "success",
            "data": {'total_publishers': int(df["total_publishers"].iloc[0])},
            "message": "出版社总数"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取出版社数量失败: {str(e)}")


@app.get("/api/analysis/price_stats", summary="获取价格统计信息")
def get_price_stats():
    """获取价格的平均值、中位数、极值等统计信息"""
    try:
        connection = get_db_connection()
        sql = "SELECT price FROM books"
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        # 计算统计量
        stats = {
            "average_price": float(df["price"].mean()),
            "median_price": float(df["price"].median()),
            "min_price": float(df["price"].min()),
            "max_price": float(df["price"].max()),
            "price_std": float(df["price"].std()),
            "price_range": f"{df['price'].min()}元 - {df['price'].max()}元"
        }

        # 确定价格集中区间
        if stats["min_price"] < 10 and stats["max_price"] < 20:
            stats["price_concentration"] = "主要集中在0-20元"
        elif stats["min_price"] < 20 and stats["max_price"] < 30:
            stats["price_concentration"] = "主要集中在10-30元"
        else:
            stats["price_concentration"] = "价格分布较分散"

        return {
            "status": "success",
            "data": stats,
            "message": "价格统计信息"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取数据失败: {str(e)}")


@app.get("/api/analysis/price_distribution", summary="获取价格区间分布")
def get_price_distribution():
    """获取价格区间分布统计"""
    try:
        connection = get_db_connection()
        sql = "SELECT price FROM books"
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        # 定义价格区间
        bins = [0, 10, 20, 30, np.inf]
        labels = ['0-10元', '10-20元', '20-30元', '30元以上']

        # 分组统计
        df['price_range'] = pd.cut(df['price'], bins=bins, labels=labels, right=False)
        distribution = df['price_range'].value_counts().to_dict()

        # 计算占比
        total = len(df)
        distribution_with_ratio = {
            range_name: {
                "count": count,
                "ratio": f"{(count / total) * 100:.2f}%"
            }
            for range_name, count in distribution.items()
        }

        return {
            "status": "success",
            "data": distribution_with_ratio,
            "message": "价格区间分布统计"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取数据失败: {str(e)}")


### 二、维度关联分析接口 ###

@app.get("/api/analysis/publisher_impact", summary="获取出版社影响力分析")
def get_publisher_impact():
    """分析各出版社的书籍数量、平均评论数、平均价格"""
    try:
        connection = get_db_connection()
        sql = """
        SELECT 
            publisher, 
            COUNT(*) as book_count,
            AVG(comments) as avg_comments,
            AVG(price) as avg_price
        FROM books
        GROUP BY publisher
        ORDER BY book_count DESC, avg_comments DESC
        """
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        # 转换为字典格式
        results = []
        for _, row in df.iterrows():
            results.append({
                "publisher": row["publisher"],
                "book_count": int(row["book_count"]),
                "avg_comments": int(row["avg_comments"]),
                "avg_price": float(row["avg_price"]),
                "impact_analysis": ""  # 可根据需要添加分析
            })

        # 添加简单分析
        if results:
            # 找出头部出版社
            top_publisher = results[0]
            top_publisher["impact_analysis"] = "头部出版社，书籍数量和平均评论数均最高"

            # 找出低价出版社
            low_price_publisher = min(results, key=lambda x: x["avg_price"])
            low_price_publisher["impact_analysis"] = "主打低价策略"

        return {
            "status": "success",
            "data": results,
            "message": "出版社影响力分析"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取数据失败: {str(e)}")


@app.get("/api/analysis/author_feedback", summary="获取作者与市场反馈关联分析")
def get_author_feedback():
    """分析作者与市场反馈的关联"""
    try:
        connection = get_db_connection()
        sql = """
        SELECT 
            author, 
            COUNT(*) as book_count,
            AVG(comments) as avg_comments,
            AVG(price) as avg_price
        FROM books
        GROUP BY author
        HAVING book_count >= 1
        ORDER BY avg_comments DESC
        """
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        # 转换为字典格式并添加分析
        results = []
        for _, row in df.iterrows():
            author_books = row["book_count"]
            avg_comments = row["avg_comments"]
            avg_price = row["avg_price"]

            analysis = ""
            # 简单分析作者IP影响
            if author_books > 1 and avg_comments > df["avg_comments"].mean():
                analysis = "可能存在作者IP溢价，多本书籍均获得较高评论"
            elif avg_price > df["avg_price"].mean() and avg_comments > df["avg_comments"].mean():
                analysis = "作品价格较高但评论数也较多，可能内容质量高"
            elif avg_price < df["avg_price"].mean() and avg_comments > df["avg_comments"].mean():
                analysis = "性价比高，低价吸引更多读者"

            results.append({
                "author": row["author"],
                "book_count": int(author_books),
                "avg_comments": float(avg_comments),
                "avg_price": float(avg_price),
                "feedback_analysis": analysis
            })

        return {
            "status": "success",
            "data": results,
            "message": "作者与市场反馈关联分析"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取数据失败: {str(e)}")


### 三、深度洞察分析接口 ###

@app.get("/api/analysis/title_keywords", summary="获取书名关键词分析")
def get_title_keywords():
    """分析书名关键词，统计高频词"""
    try:
        connection = get_db_connection()
        sql = "SELECT title FROM books"
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        # 提取关键词（简单示例：按空格和特殊符号分割）
        all_keywords = []
        for title in df["title"]:
            # 简单处理：去除特殊符号并分割
            title = str(title).replace("(", "").replace(")", "").replace("：", ":").replace("《", "").replace("》", "")
            keywords = title.split()
            all_keywords.extend(keywords)

        # 统计高频词（排除常见无意义词）
        stop_words = {"的", "了", "是", "在", "和", "与", "及", "或", "著", "编著", "出版社", "修订版", "精装版"}
        keyword_counts = {}

        for keyword in all_keywords:
            if len(keyword) < 2 or keyword in stop_words:
                continue
            if keyword in keyword_counts:
                keyword_counts[keyword] += 1
            else:
                keyword_counts[keyword] = 1

        # 按频率排序
        sorted_keywords = sorted(keyword_counts.items(), key=lambda x: x[1], reverse=True)

        # 分析热门题材
        hot_topics = []
        if sorted_keywords:
            top_keywords = [k for k, v in sorted_keywords if v > 1]
            if "文学" in top_keywords:
                hot_topics.append("文学理论")
            if "写作" in top_keywords or "诗词" in top_keywords:
                hot_topics.append("写作指导")
            if "传" in top_keywords or "自传" in top_keywords:
                hot_topics.append("人物传记")

        return {
            "status": "success",
            "data": {
                "top_keywords": sorted_keywords[:10],  # 只返回前10个高频词
                "hot_topics": hot_topics,
                "total_keywords": len(all_keywords)
            },
            "message": "书名关键词分析"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取数据失败: {str(e)}")


### 四、异常值分析接口 ###

@app.get("/api/analysis/outliers", summary="获取异常值分析")
def get_outliers():
    """识别数据中的异常点"""
    try:
        connection = get_db_connection()
        sql = "SELECT title, price, comments FROM books"
        df = pd.read_sql(sql, connection)
        connection.close()

        if df.empty:
            return {"message": "没有找到数据"}

        # 计算评论数和价格的均值和标准差
        avg_comments = df["comments"].mean()
        std_comments = df["comments"].std()
        avg_price = df["price"].mean()
        std_price = df["price"].std()

        # 定义异常值阈值（这里使用均值±2倍标准差）
        comments_lower = avg_comments - 2 * std_comments
        comments_upper = avg_comments + 2 * std_comments
        price_lower = avg_price - 2 * std_price
        price_upper = avg_price + 2 * std_price

        # 找出异常值
        outliers = []
        for _, row in df.iterrows():
            title = row["title"]
            price = row["price"]
            comments = row["comments"]

            analysis = []
            is_outlier = False

            # 评论数异常
            if comments < comments_lower:
                analysis.append(f"评论数远低于平均水平（{comments} < {avg_comments:.2f}）")
                is_outlier = True
            elif comments > comments_upper:
                analysis.append(f"评论数远高于平均水平（{comments} > {avg_comments:.2f}）")
                is_outlier = True

            # 价格异常
            if price < price_lower:
                analysis.append(f"价格远低于平均水平（{price} < {avg_price:.2f}）")
                is_outlier = True
            elif price > price_upper:
                analysis.append(f"价格远高于平均水平（{price} > {avg_price:.2f}）")
                is_outlier = True

            if is_outlier:
                outliers.append({
                    "title": title,
                    "price": float(price),
                    "comments": int(comments),
                    "outlier_analysis": analysis
                })

        return {
            "status": "success",
            "data": {
                "outliers": outliers,
                "avg_comments": float(avg_comments),
                "std_comments": float(std_comments),
                "avg_price": float(avg_price),
                "std_price": float(std_price)
            },
            "message": "异常值分析结果"
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取数据失败: {str(e)}")


### 启动应用 ###
if __name__ == "__main__":
    import uvicorn

    uvicorn.run(app, host="0.0.0.0", port=8000)